Filter Data
From Documentation
Filter is a useful feature especially when you want to focus on a subset of data. It can filter out of those data you don't want displaying without removing them. Spreadsheet allows you to enable/disable filter and apply/reset criteria via API. You can use both Range or SheetOperationUtil to achieve these functions, but SheetOperationUtil
checks sheet protection for you.
We'll use a simple application to demonstrate filter API. Its screenshot is as below:
In this application, through those buttons on the right hand side we can toggle, clear, and reapply filters and filter data by "Type" column.
Here is the source code to implement it:
public class AutoFilterComposer extends SelectorComposer<Component> {
@Wire
private Spreadsheet ss;
@Wire
private Combobox typeBox;
@Listen("onClick = button[label='Toogle Filter']")
public void toggle() {
Range selection = Ranges.range(ss.getSelectedSheet(), ss.getSelection());
Range filteringRange = selection.findAutoFilterRange();
if(!selection.isAutoFilterEnabled() && filteringRange == null) {
Messagebox.show("nothing to filter");
return;
}
selection.enableAutoFilter(!selection.isAutoFilterEnabled());
// SheetOperationUtil.toggleAutoFilter(selection);
}
@Listen("onClick = button[label='Clear Filter']")
public void clear() {
Range sheetRange = Ranges.range(ss.getSelectedSheet());
sheetRange.resetAutoFilter();
// SheetOperationUtil.resetAutoFilter(selection);
}
@Listen("onClick = button[label='Reapply Filter']")
public void reapply() {
Range sheetRange = Ranges.range(ss.getSelectedSheet());
sheetRange.applyAutoFilter();
// SheetOperationUtil.applyAutoFilter(selection);
}
@Listen("onClick = button[label='Apply']")
public void apply() {
Range sheetRange = Ranges.range(ss.getSelectedSheet());
if (sheetRange.isAutoFilterEnabled()){
String[] criteria = {typeBox.getValue()};
sheetRange.enableAutoFilter(1, AutoFilterOperation.VALUES, criteria, null,
true);
}
}
}
- Line 12: The method
findAutoFilterRange()
will return a non-blank area based on your selection if Spreadsheet can find it. We should use it to ensure not to enable filter if there is no data. - Line 13: Use
isAutoFilterEnabled()
to get enabled status of the auto filter. - Line 18: Enable auto filter with
enableAutoFilter(true)
or disable it withenableAutoFilter(false)
. - LIne 20: You can also enable / disable auto filter with
SheetOperationUtil.toggleAutoFilter()
. - Line 26, 28: The method
resetAutoFilter()
clears applied criteria and all data will be shown andSheetOperationUtil.resetAutoFilter()
has the same function. - Line 34,36: The method
applyAutoFilter()
will re-apply current criteria to modified or newly added data. - Line 43: The argument for criteria is a String array, and you can have multiple values in it, e.g.
{"Beverages", "Meat", "Tools"}
- Line 44: Apply criteria on "Type" column which is the first column, so the first argument is
1
. The second argument is the filter operation the filter performs, and we currently only support filtering values. The fourth argument determines drop-down arrow's visibility which is usuallytrue
.
All source code listed in this book is at Github.